﻿using Flare.DB.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;

namespace Flare.DB
{
    class equipments
    {
        public IEnumerable<Entity.EquipmentView> Index(string search_string = null, bool last2up = true, bool hideOlder = true, int? id_department = null,
                 int? id_room = null, int? id_type = null, int? id_mark = null, int? id_model = null)
        {
            var result = from e in Source.equipments()
                         join ml in Source.equipment_model() on e.id_model equals ml.id_model
                         join m in Source.equipment_mark() on ml.id_mark equals m.id_mark
                         join t in Source.equipment_type() on m.id_type equals t.id_type
                         join r in Source.general_room() on e.id_room equals r.id_room
                         join d in Source.general_department() on r.id_department equals d.id_department

                         where (search_string == null ||
                         e.inventory_number.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1 ||
                         e.factory_number.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1) &&
                         (id_department == null || d.id_department == id_department) &&
                         (id_room == null || r.id_room == id_room) &&
                         (id_type == null || t.id_type == id_type) &&
                         (id_mark == null || m.id_mark == id_mark) &&
                         (id_model == null || ml.id_model == id_model)

                         select new Entity.EquipmentView
                         {
                             id_equipment = e.id_equipment,
                             id_type = t.id_type,
                             mark_model = string.Format("{0} {1}", m.mark, ml.model),
                             teh_type = t.type,
                             inventory_number = e.inventory_number,
                             room = (r.room == d.department) ? r.room : string.Format("{0}/{1}", d.department, r.room)
                         };

            if (last2up) result = result.OrderByDescending(m => m.id_equipment);
            return result.Take(40);
        }

        public void Create(CeEntity.equipments data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"INSERT INTO [equipments] ([id_model],[id_room],[factory_number],[inventory_number],
                                                           [description],[date_create]) VALUES(@id_model,@id_room,@factory_number,@inventory_number,
                                                           @description,@date_create)", connection))
                {
                    command.Parameters.Add("@id_model", SqlDbType.Int).Value = data.id_model;
                    command.Parameters.Add("@id_room", SqlDbType.Int).Value = data.id_room;
                    command.Parameters.Add("@factory_number", SqlDbType.VarChar).Value = (string.IsNullOrEmpty(data.factory_number)) ? DBNull.Value : (object)data.factory_number;
                    command.Parameters.Add("@inventory_number", SqlDbType.VarChar).Value = (string.IsNullOrEmpty(data.inventory_number)) ? DBNull.Value : (object)data.inventory_number;
                    command.Parameters.Add("@description", SqlDbType.VarChar).Value = (string.IsNullOrEmpty(data.description)) ? DBNull.Value : (object)data.description;
                    command.Parameters.Add("@date_create", SqlDbType.Date).Value = data.date_create;
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время добавления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Edit(CeEntity.equipments data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"UPDATE [equipments] SET [id_model]=@id_model,[id_room]=@id_room,[factory_number]=@factory_number,
                                                           [inventory_number]=@inventory_number,[description]=@description WHERE 
                                                           [id_equipment]=@id_equipment", connection))
                {
                    command.Parameters.Add("@id_equipment", SqlDbType.Int).Value = data.id_equipment;
                    command.Parameters.Add("@id_model", SqlDbType.Int).Value = data.id_model;
                    command.Parameters.Add("@id_room", SqlDbType.Int).Value = data.id_room;
                    command.Parameters.Add("@factory_number", SqlDbType.NVarChar).Value = (string.IsNullOrEmpty(data.factory_number)) ? DBNull.Value : (object)data.factory_number;
                    command.Parameters.Add("@inventory_number", SqlDbType.NVarChar).Value = (string.IsNullOrEmpty(data.inventory_number)) ? DBNull.Value : (object)data.inventory_number;
                    command.Parameters.Add("@description", SqlDbType.NVarChar).Value = (string.IsNullOrEmpty(data.description)) ? DBNull.Value : (object)data.description;
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время обновления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Delete(int? id_equipment)
        {
            if (id_equipment.HasValue == false)
            {
                throw new Exception("[ИД оборудования] должно иметь значение");
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"DELETE FROM [equipments_move_history] WHERE [id_equipment]=@id_equipment", connection))
                {
                    command.Parameters.Add("@id_equipment", SqlDbType.Int).Value = id_equipment;
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время удаления данных произошла ошибка. Данные не сохранены");
                    }
                }
                using (SqlCeCommand command = new SqlCeCommand(@"DELETE FROM [equipments] WHERE [id_equipment]=@id_equipment", connection))
                {
                    command.Parameters.Add("@id_equipment", SqlDbType.Int).Value = id_equipment;
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время удаления данных произошла ошибка. Данные не сохранены");
                    }
                }

            }
        }

        public void Move(int? id_equipment, int? id_room_old, int? id_room, string description)
        {
            if (id_equipment.HasValue == false)
            {
                throw new Exception("[ИД оборудования] должно иметь значение");
            }
            if (id_room.HasValue == false || id_room == -1)
            {
                throw new Exception("Поле [Кабинет] должно иметь значение");
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"INSERT INTO [equipments_move_history] ([id_equipment],[move_date],[from_room],[to_room],
                                                               [description]) VALUES (@id_equipment,@move_date, 
                                                               (SELECT TOP 1 [room] FROM [general_room] WHERE [id_room] = @id_room_old),
                                                               (SELECT TOP 1 [room] FROM [general_room] WHERE [id_room] = @id_room),@description)", connection))
                {
                    command.Parameters.Add("@id_equipment", SqlDbType.Int).Value = id_equipment;
                    command.Parameters.Add("@move_date", SqlDbType.DateTime).Value = DateTime.Now;
                    command.Parameters.Add("@description", SqlDbType.NVarChar).Value = (string.IsNullOrEmpty(description)) ? DBNull.Value : (object)description;
                    command.Parameters.Add("@id_room", SqlDbType.Int).Value = id_room;
                    command.Parameters.Add("@id_room_old", SqlDbType.Int).Value = id_room_old;
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время перемещения данных произошла ошибка. Данные не сохранены");
                    }
                }

                using (SqlCeCommand command = new SqlCeCommand(@"UPDATE [equipments] SET [id_room] = @id_room WHERE id_equipment=@id_equipment", connection))
                {
                    command.Parameters.Add("@id_equipment", SqlDbType.Int).Value = id_equipment;
                    command.Parameters.Add("@move_date", SqlDbType.DateTime).Value = DateTime.Now;
                    command.Parameters.Add("@description", SqlDbType.NVarChar).Value = (string.IsNullOrEmpty(description)) ? DBNull.Value : (object)description;
                    command.Parameters.Add("@id_room", SqlDbType.Int).Value = id_room;
                    command.Parameters.Add("@id_room_old", SqlDbType.Int).Value = id_room_old;
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время перемещения данных произошла ошибка. Данные не сохранены");
                    }
                }

                
            }
        }

        public EquipmentDetails Details(int id_equipment)
        {
            var result = (from e in Source.equipments()
                          join ml in Source.equipment_model() on e.id_model equals ml.id_model
                          join m in Source.equipment_mark() on ml.id_mark equals m.id_mark
                          join t in Source.equipment_type() on m.id_type equals t.id_type
                          join r in Source.general_room() on e.id_room equals r.id_room
                          join d in Source.general_department() on r.id_department equals d.id_department

                          where e.id_equipment == id_equipment

                          select new EquipmentDetails
                          {
                              id_equipment = e.id_equipment,
                              id_type = t.id_type,
                              type = t.type,
                              id_mark = m.id_mark,
                              mark = m.mark,
                              id_model = ml.id_model,
                              model = ml.model,
                              id_room = r.id_room,
                              room = r.room,
                              id_department = d.id_department,
                              department = d.department,
                              inventory_number = e.inventory_number,
                              factory_number = e.factory_number,
                              description = e.description,
                              date_create = e.date_create.Value
                          }).First();

            var history = from h in Source.equipments_move_history()
                          where h.id_equipment == id_equipment
                          select h;
            result.move_history = history;
            return result;
        }

        public List<Entity.EquipmentMoveHistory> MoveHistory(string filter = null)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"SELECT TOP 80 [move_date],[tech_name],[inventory_number],[room],[description],[author]
                        FROM [view_eq_move_history] WHERE 
                        [inventory_number] LIKE @filter OR 
                        [room] LIKE @filter OR 
                        @filter IS NULL ORDER BY [move_date] DESC", connection))
                {
                    command.Parameters.AddWithValue("@filter", (filter == null) ? DBNull.Value : (object)("%" + filter + "%"));
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    var history = new List<Entity.EquipmentMoveHistory>();
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var current_history = new Entity.EquipmentMoveHistory();
                                current_history.move_date = Convert.ToDateTime(reader["move_date"]);
                                current_history.inventory_number = reader["inventory_number"].ToString();
                                current_history.tech_name = reader["tech_name"].ToString();
                                current_history.room = reader["room"].ToString();
                                current_history.author = reader["author"].ToString();
                                current_history.description = reader["description"].ToString();
                                history.Add(current_history);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                    return history;
                }
            }
        }
    }
}
